import * as XLSX from 'xlsx'; // npm install xlsx
/**
 * 将表头数组和内容数组转换成一个Excel文件的模板字符串
 * @param {Object} headers 标题数组
 * @param {Object} listData 表格数据的二维数组
 * @param {Object} sheetName sheet的名称
 */
export function getExcelOneSheetStr(headers, listData, sheetName = 'sheet1') {
	let str = getSheetStr(headers, listData)
	let template =
		`
<html xmlns:o="urn:schemas-microsoft-com:office:office" 	 
		 xmlns:x="urn:schemas-microsoft-com:office:excel"   
		 xmlns="http://www.w3.org/TR/REC-html40">
    <head><!--[if gte mso 9]>
		<xml encoding="UTF-8">
			<x:ExcelWorkbook>
				<x:ExcelWorksheets>
					<x:ExcelWorksheet>
						<x:Name>${sheetName}</x:Name>
					<x:WorksheetOptions>
					<x:DisplayGridlines/>
					</x:WorksheetOptions>
					</x:ExcelWorksheet>
				</x:ExcelWorksheets>
			</x:ExcelWorkbook>
		</xml><![endif]-->
	</head>
	<body>
		<table>${str}</table>
	</body>
</html>`
	return template
}
/**
 * 将JSON数组导出成Excel的Base64字符串，
 * @param {Object} dataList json数组@
 * @param {Object} sheetName 表单名称 默认为sheet1
 */
export function getExcelOneSheetBase64Str(dataList, sheetName = 'sheet1') {
	const ws = XLSX.utils.json_to_sheet(dataList);
	const workbook = XLSX.utils.book_new();
	XLSX.utils.book_append_sheet(workbook, ws, sheetName);
	const excelBase64 = XLSX.write(workbook, {
		bookType: 'xlsx',
		type: 'base64'
	});
	return excelBase64
}
/**
 * 得到一个Excel中多个sheet的模板字符串
 * @param {Object} JsonList [ {headers, listData, sheetName}]
 */
export function getMutilSheetStr(JsonList) {
	let str = getSheetStr(headers, listData)
	let template =
		`<html xmlns:o="urn:schemas-microsoft-com:office:office" 	 
		 xmlns:x="urn:schemas-microsoft-com:office:excel"   
		 xmlns="http://www.w3.org/TR/REC-html40">
    <head><!--[if gte mso 9]>
		<xml encoding="UTF-8">
			<x:ExcelWorkbook>
				<x:ExcelWorksheets>
					<x:ExcelWorksheet>
						<x:Name>${sheetName+"3333"}</x:Name>
					<x:WorksheetOptions>
					<x:DisplayGridlines/>
					</x:WorksheetOptions>
					</x:ExcelWorksheet>
				</x:ExcelWorksheets>
			</x:ExcelWorkbook>
		</xml><![endif]-->
	</head>
	<body>
		<table>${str}</table>
	</body>
</html>`
	return template
}

/**
 * 将多个表单数据JSON导出成Excel多个sheet形式的Base64字符串，
 * @param {Object} sheetDataList 数据数组，数组一项包含sheetName:表单名称,dataList:表单json数据
 */
export function getExcelMutilSheetBase64Str(sheetDataList) {
	const workbook = XLSX.utils.book_new();
	for (var i = 0; i < sheetDataList.length; i++) {
		let sheetData = sheetDataList[i]
		const ws = XLSX.utils.json_to_sheet(sheetData.dataList);
		XLSX.utils.book_append_sheet(workbook, ws, sheetData.sheetName || ("sheet" + i));
	}
	const excelBase64 = XLSX.write(workbook, {
		bookType: 'xlsx',
		type: 'base64'
	});
}
/**
 * 将标题数组和表格数组转成Excel中一个sheet对应的模板字符串
 * @param {Object} headers 标题数组
 * @param {Object} listData 表格内容的二维数组
 */
export function getSheetStr(headers, listData) {
	let str = "" //
	if (headers && headers.length > 0) { //导出表头
		str += getTableRowStr(headers)
	}
	for (let i = 0; i < listData.length; i++) { // 循环遍历，每行加入tr标签，每个单元格加td标签
		str += getTableRowStr(listData[i])
	}
	return str
}

/**
 * 将一个数组 转成Excel中一行对应的模板字符串
 * @param {Object} tableRowData 
 */
function getTableRowStr(tableRowData) {
	let str = '<tr>'
	for (let item of tableRowData) {
		// 增加\t为了不让表格显示科学计数法或者其他格式
		str += `<td style="text-align: center">${item}</td>`
	}
	str += '</tr>'
	return str
}
/**
 * 将JSON格式的List转成表格可以直接导入的数据数组
 * @param {Object} headers 导出表格的标题 
 * @param {Object} header2FieldMap 标题到字段的映射Map
 * @param {Object} dataJsonList 需要转换的JSON数据List
 */
export function dataListToSheetDataArr(headers, header2FieldMap, dataJsonList) {
	let sheetArr = []
	for (let dataJson of dataJsonList) {
		let dataRow = []
		for (let header of headers) {
			let field = header2FieldMap[header]
			dataRow.push(dataJson[field])
		}
		sheetArr.push(dataRow)
	}
	return sheetArr
}

/**
 * 将excel的base64字符串转成workbook
 * @param {Object} excelBase64
 */
export function parseExcelBase64ToWorkbook(excelBase64) { //将Base64的Excel文件数据转换成workbook对象方便后续操作
	const base64 = excelBase64.split(',')[1]; // 移除Data URL前缀
	const arrayBuffer = uni.base64ToArrayBuffer(base64); // 转换为ArrayBuffer
	let workbook = XLSX.read(arrayBuffer, {
		type: 'array'
	});
	return workbook
}
/**
 * 将一个workbook的某一个sheet数据转换成Json数组
 */
export function parseXlsx(workbook, index = 0) {
	const worksheet = workbook.Sheets[workbook.SheetNames[index]]
	// 转换为JSON数据并返回
	const tableData = XLSX.utils.sheet_to_json(worksheet, {
		defval: '',
		raw: false
	})
	return tableData;
}